const express = require('express');
const router = express.Router();
const pool = require('./db.js');
const multer = require('multer');
const XLSX = require('xlsx');
const { decryptForUser } = require('./userDataEncryption.js');
const upload = multer({
    storage: multer.memoryStorage(), // 使用内存存储获取Buffer
    limits: { fileSize: 20 * 1024 * 1024 } // 限制20MB
  });

// 创建练习
router.post('/api/create-practice', async (req, res) => {
    const { practice_id, practice_name, course_code, start_time, deadline, submit_times } = req.body;
    
    // 数据验证
    if (!practice_id || !practice_name || !course_code || !start_time || !deadline) {
        return res.status(400).json({ 
            success: false, 
            message: '缺少必填字段: practice_id, practice_name, course_code, start_time或deadline' 
        });
    }

    // 验证练习名称长度
    if (practice_name.length < 2 || practice_name.length > 50) {
        return res.status(400).json({ 
            success: false, 
            message: '练习名称长度应在2-50个字符之间' 
        });
    }

    // 验证日期格式
    const dateRegex = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/;
    if (!dateRegex.test(start_time) || !dateRegex.test(deadline)) {
        return res.status(400).json({ 
            success: false, 
            message: '日期格式不正确，应为YYYY-MM-DD HH:MM:SS' 
        });
    }

    try {
        await pool.execute(
            'INSERT INTO practice (practice_id, practice_name, course_code, start_time, deadline, submit_times) VALUES (?, ?, ?, ?, ?, ?)',
            [practice_id, practice_name, course_code, start_time, deadline, submit_times || null]
        );
        res.json({ success: true, message: '练习创建成功' });
    } catch (error) {
        console.error('Error creating practice:', error);
        res.status(500).json({ 
            success: false, 
            message: '创建练习失败',
            error: error.message 
        });
    }
});

// 获取教师创建的练习
router.get('/api/get-practices/:teacherId', async (req, res) => {
    const { teacherId } = req.params;
    //console.log(`接收到的教师 ID: ${teacherId}`);

    try {
        const sql = `
            SELECT p.*, c.course_name 
            FROM practice p
            JOIN courses c ON p.course_code = c.course_code
            WHERE c.teacher_user_id = ?
            ORDER BY p.deadline DESC
        `;

        const [practices] = await pool.execute(sql, [teacherId]);

        res.json(practices);
    } catch (error) {
        console.error('Error getting practices:', error);
        res.status(500).json({ message: '获取练习失败' });
    }
});

// 获取练习题目
router.get('/api/practice/:practiceId/questions', async (req, res) => {
    const { practiceId } = req.params;
    
    try {
        // 获取选择题
        const [choiceQuestions] = await pool.execute(`
            SELECT * FROM choice_questions 
            WHERE practice_id = ?
            ORDER BY question_order
        `, [practiceId]);
        
        // 获取填空题
        const [fillQuestions] = await pool.execute(`
            SELECT * FROM fill_questions 
            WHERE practice_id = ?
            ORDER BY question_order
        `, [practiceId]);
        
        // 获取解答题
        const [essayQuestions] = await pool.execute(`
            SELECT * FROM essay_questions 
            WHERE practice_id = ?
            ORDER BY question_order
        `, [practiceId]);
        
        res.json({
            success: true,
            data: {
                choice_questions: choiceQuestions,
                fill_questions: fillQuestions,
                essay_questions: essayQuestions
            }
        });
    } catch (error) {
        console.error('Error getting practice questions:', error);
        res.status(500).json({ 
            success: false,
            message: '获取练习题目失败'
        });
    }
});

// 删除练习
router.delete('/api/practice/:practiceId', async (req, res) => {
    const { practiceId } = req.params;
    
    try {
        // 先删除关联的练习
        await pool.execute('DELETE FROM choice_questions WHERE practice_id = ?', [practiceId]);
        await pool.execute('DELETE FROM fill_questions WHERE practice_id = ?', [practiceId]);
        await pool.execute('DELETE FROM essay_questions WHERE practice_id = ?', [practiceId]);
        await pool.execute('DELETE FROM commited_choice_question WHERE practice_id = ?', [practiceId]);
        await pool.execute('DELETE FROM commited_fill_question WHERE practice_id = ?', [practiceId]);
        await pool.execute('DELETE FROM commited_essay_question WHERE practice_id = ?', [practiceId]);
        await pool.execute('DELETE FROM commited_practice WHERE practice_id = ?', [practiceId]);
        await pool.execute('DELETE FROM incorrect_question WHERE practice_id = ?', [practiceId]);

        // 再删除练习
        const [result] = await pool.execute('DELETE FROM practice WHERE practice_id = ?', [practiceId]);
        
        if (result.affectedRows === 0) {
            return res.status(404).json({ 
                success: false,
                message: '未找到指定练习'
            });
        }
        
        res.json({ 
            success: true,
            message: '练习删除成功'
        });
    } catch (error) {
        console.error('Error deleting practice:', error);
        console.error('SQL Error:', error.sql); // 如果使用mysql2
        res.status(500).json({ 
            success: false,
            message: '删除练习失败'
        });
    }
});

// 获取练习总分值
router.get('/api/practice/:practiceId/total-score', async (req, res) => {
    const { practiceId } = req.params;
    
    try {
        // 计算选择题总分
        const [choiceScores] = await pool.execute(`
            SELECT SUM(score) as total 
            FROM choice_questions 
            WHERE practice_id = ?
        `, [practiceId]);
        
        // 计算填空题总分，处理NULL值
        const [fillScores] = await pool.execute(`
            SELECT SUM(
                COALESCE(blank1_score, 0) + 
                COALESCE(blank2_score, 0) + 
                COALESCE(blank3_score, 0)
            ) as total 
            FROM fill_questions 
            WHERE practice_id = ?
        `, [practiceId]);
        
        // 计算解答题总分
        const [essayScores] = await pool.execute(`
            SELECT SUM(score) as total 
            FROM essay_questions 
            WHERE practice_id = ?
        `, [practiceId]);
        
        // 计算总分，确保转换为数值类型
        const totalScore = 
            Number(choiceScores[0].total || 0) + 
            Number(fillScores[0].total || 0) + 
            Number(essayScores[0].total || 0);
        
        res.json({
            success: true,
            data: {
                total_score: totalScore
            }
        });
    } catch (error) {
        console.error('Error calculating total score:', error);
        res.status(500).json({ 
            success: false,
            message: '计算总分失败'
        });
    }
});

// 导入题目接口 - 修改为返回解析的题目数据而不是直接存入数据库
router.post('/api/import-practice',upload.single('importFile'), async (req, res) => {
    try {
      if (!req.file) {
        return res.status(400).json({ error: '未上传文件' });
      }
  
      const file = req.file;
      const workbook = XLSX.read(file.buffer, { type: 'buffer' });
      
      const parsedQuestions = {
        choice: [],
        fill: [],
        essay: []
      };
  
      // 解析选择题
      const choiceSheet = workbook.Sheets[workbook.SheetNames[0]];
      const choiceData = XLSX.utils.sheet_to_json(choiceSheet);
      
      choiceData.forEach(row => {
        // 验证并处理选择题数据
        if (row.题目 && row.选项A && row.选项B && row.选项C && row.选项D && ['A','B','C','D'].includes(row.正确答案?.toString().trim().toUpperCase()) && parseInt(row.分值)>=1) {
          if(row.正确答案){
            parsedQuestions.choice.push({
            questionText: row.题目,
            options: [row.选项A, row.选项B, row.选项C, row.选项D],
            answer: row.正确答案.toUpperCase(),
            score: parseInt(row.分值)
          });
        }
        }
      });
  
      // 解析填空题
        if (workbook.SheetNames.length > 1) {
            const fillSheet = workbook.Sheets[workbook.SheetNames[1]];
            const fillData = XLSX.utils.sheet_to_json(fillSheet);
    
            fillData.forEach(row => {
                if (row.题目) {
                    // 根据题干中的__数量确定应有空数
                    const blankCount = (row.题目.match(/__/g) || []).length;
            
                    // 验证必要字段
                    const blanks = [];
                    let isValid = true;
            
                    // 遍历每个空（从1开始）
                    for (let i = 1; i <= blankCount; i++) {
                        const answerKey = `空${i}答案`;
                        const scoreKey = `空${i}分值`;
                
                        // 验证答案和分值存在且有效
                        if (!row[answerKey] || 
                            isNaN(parseInt(row[scoreKey])) || 
                            parseInt(row[scoreKey]) < 1
                        ) {
                            isValid = false;
                            //console.warn(`第${i}空数据不完整或无效，题目：${row.题目}`);
                            break;
                        }
                
                        blanks.push({
                            answer: row[answerKey],
                            score: parseInt(row[scoreKey])
                        });
                    }

                    // 确保：1. 有至少一个空 2. 实际解析空数匹配题干空数 3. 所有空数据有效
                    if (blankCount > 0 && blanks.length === blankCount && isValid) {
                        parsedQuestions.fill.push({
                            questionText: row.题目,
                            blanksCount: blankCount,
                            answers: blanks.map(b => b.answer),
                            scores: blanks.map(b => b.score)
                        });
                    } else {
                        /*console.warn(`跳过无效填空题：${row.题目}`, {
                            题干空数: blankCount,
                            实际解析空数: blanks.length,
                            是否有效: isValid
                        });*/
                    }
                }
            });
        }
  
      // 解析解答题
      if (workbook.SheetNames.length > 2) {
        const essaySheet = workbook.Sheets[workbook.SheetNames[2]];
        const essayData = XLSX.utils.sheet_to_json(essaySheet);
        
        essayData.forEach(row => {
          if (row.题目 && parseInt(row.分值)>=1) {
            parsedQuestions.essay.push({
              questionText: row.题目,
              score: parseInt(row.分值)
            });
          }
        });
      }
  
      // 添加统计计算
      const totalCount=parsedQuestions.choice.length+parsedQuestions.fill.length+parsedQuestions.essay.length;

  // 修改返回结构
  return res.status(200).json({
      success: true,
      totalCount,
      choiceCount: parsedQuestions.choice.length,
      fillCount: parsedQuestions.fill.length,
      essayCount: parsedQuestions.essay.length,
      questions: parsedQuestions // 如果需要返回题目数据可以保留
  });
    } catch (error) {
      console.error('导入题目错误:', error);
      return res.status(500).json({ error: '导入失败，请检查文件格式' });
    }
  });

// 删除单个题目
router.delete('/api/question/:questionId', async (req, res) => {
    const { questionId } = req.params;
    const { type } = req.body;
    
    if (!type || !['choice', 'fill', 'essay'].includes(type)) {
        return res.status(400).json({
            success: false,
            message: '必须指定有效的题目类型(choice/fill/essay)'
        });
    }

    try {
        let tableName;
        switch(type) {
            case 'choice':
                tableName = 'choice_questions';
                break;
            case 'fill':
                tableName = 'fill_questions';
                break;
            case 'essay':
                tableName = 'essay_questions';
                break;
        }

        const [result] = await pool.execute(
            `DELETE FROM ${tableName} WHERE id = ?`,
            [questionId]
        );

        if (result.affectedRows === 0) {
            return res.status(404).json({
                success: false,
                message: '未找到指定题目'
            });
        }

        res.json({
            success: true,
            message: '题目删除成功'
        });
    } catch (error) {
        console.error('删除题目失败:', error);
        res.status(500).json({
            success: false,
            message: '删除题目失败'
        });
    }
});

// 获取课程对应的练习的接口
router.get('/practice/course/:courseCode', async (req, res) => {
    const courseCode = req.params.courseCode;
    try {
        const [rows] = await pool.execute('SELECT * FROM practice WHERE course_code = ?', [courseCode]);
        res.json(rows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '服务器错误' });
    }
});

// 获取特定练习特定题号的选择题
router.get('/api/practice/:practiceId/choice-question/:questionOrder', async (req, res) => {
    const { practiceId, questionOrder } = req.params;
    try {
        const [choiceQuestion] = await pool.execute(`
            SELECT * FROM choice_questions 
            WHERE practice_id = ? AND question_order = ?
        `, [practiceId, questionOrder]);
        res.json({ success: true, data: choiceQuestion[0] });
    } catch (error) {
        console.error('Error getting choice question:', error);
        res.status(500).json({ success: false, message: '获取选择题失败' });
    }
});

// 获取特定练习特定题号的填空题
router.get('/api/practice/:practiceId/fill-question/:questionOrder', async (req, res) => {
    const { practiceId, questionOrder } = req.params;
    try {
        const [fillQuestion] = await pool.execute(`
            SELECT * FROM fill_questions 
            WHERE practice_id = ? AND question_order = ?
        `, [practiceId, questionOrder]);
        res.json({ success: true, data: fillQuestion[0] });
    } catch (error) {
        console.error('Error getting fill question:', error);
        res.status(500).json({ success: false, message: '获取填空题失败' });
    }
});

// 获取特定练习特定题号的解答题
router.get('/api/practice/:practiceId/essay-question/:questionOrder', async (req, res) => {
    const { practiceId, questionOrder } = req.params;
    //console.log(req.params);
    try {
        const [essayQuestion] = await pool.execute(`
            SELECT * FROM essay_questions 
            WHERE practice_id = ? AND question_order = ?
        `, [practiceId, questionOrder]);
        res.json({ success: true, data: essayQuestion[0] });
    } catch (error) {
        console.error('Error getting essay question:', error);
        res.status(500).json({ success: false, message: '获取解答题失败' });
    }
});

// 获取单个练习的详细信息（包括剩余提交次数）
router.get('/practice/detail/:practiceId', async (req, res) => {
    const practiceId = req.params.practiceId;
    const userId = req.query.userId; // 获取用户ID参数
    
    try {
        // 查询练习基本信息
        const [practiceRows] = await pool.execute(
            'SELECT * FROM practice WHERE practice_id = ?', 
            [practiceId]
        );
        
        if (practiceRows.length === 0) {
            return res.status(404).json({ error: '练习不存在' });
        }
        
        const practice = practiceRows[0];
        
        // 如果未提供用户ID，直接返回练习信息
        if (!userId) {
            practice.remaining_submit_times = practice.submit_times; // 默认显示最大提交次数
            return res.json(practice);
        }
        
        // 查询用户的提交记录
        const [commitRows] = await pool.execute(
            'SELECT remaining_submit_times FROM commited_practice WHERE practice_id = ? AND commit_user_id = ?',
            [practiceId, userId]
        );
        
        // 设置剩余提交次数
        if (commitRows.length > 0) {
            practice.remaining_submit_times = commitRows[0].remaining_submit_times;
        } else {
            practice.remaining_submit_times = practice.submit_times; // 初始值
        }
        
        res.json(practice);
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '服务器错误' });
    }
});

// 提交练习的接口
router.post('/submit-practice', async (req, res) => {    
    const { practice_id, user_id } = req.body;
    try {        
        // 检查练习是否存在        
        const [practiceRows] = await pool.execute('SELECT * FROM practice WHERE practice_id = ?', [practice_id]);        
        if(practiceRows.length === 0) {            
            return res.status(404).json({ message: '练习不存在，不允许提交' });
        }
        const practice = practiceRows[0];
        
        // 检查是否已有提交记录        
        const [commitRows] = await pool.execute('SELECT * FROM commited_practice WHERE practice_id = ? AND commit_user_id = ?', [practice_id, user_id]);
        
        // 获取当前时间
        const currentTime = new Date();
        
        if (commitRows.length === 0) {            
            // 若不存在提交记录，插入新记录并记录首次提交时间
            const remaining_submit_times = practice.submit_times - 1;            
            await pool.execute(
                'INSERT INTO commited_practice (practice_id, commit_user_id, remaining_submit_times, submit_time) VALUES (?, ?, ?, ?)', 
                [practice_id, user_id, remaining_submit_times, currentTime]
            );            
            return res.status(200).json({ message: '提交成功', remaining_submit_times });
        }
        
        const commit = commitRows[0];        
        if (commit.remaining_submit_times === 0) {            
            return res.status(403).json({ message: '剩余提交次数为 0，不允许提交' });
        }
        
        // 更新提交记录，减少剩余提交次数并更新提交时间
        const newRemainingTimes = commit.remaining_submit_times - 1;        
        await pool.execute(
            'UPDATE commited_practice SET remaining_submit_times = ?, submit_time = ? WHERE practice_id = ? AND commit_user_id = ?', 
            [newRemainingTimes, currentTime, practice_id, user_id]
        );        
        return res.status(200).json({ message: '提交成功', remaining_submit_times: newRemainingTimes });
    } catch (error) {        
        console.error('提交练习时出错:', error);        
        return res.status(500).json({ message: '服务器内部错误' });
    }
});


// 获取指定练习的提交记录（修改部分）
router.get('/api/commited-practice/:practiceId', async (req, res) => {
    try {
        const { practiceId } = req.params;
        
        // 查询提交记录及关联信息
        const submissionQuery = `
            SELECT 
                cp.practice_id,
                cp.commit_user_id,
                DATE_FORMAT(cp.submit_time, '%Y-%m-%d %H:%i:%s') AS submit_time,
                DATE_FORMAT(p.deadline, '%Y-%m-%d %H:%i:%s') AS deadline,
                u.name,
                u.student_id,
                u.user_id,  
                p.practice_name,
                p.course_code,
                c.course_name
            FROM 
                commited_practice cp
            JOIN 
                users u ON cp.commit_user_id = u.user_id
            JOIN 
                practice p ON cp.practice_id = p.practice_id
            JOIN 
                courses c ON p.course_code = c.course_code
            WHERE 
                cp.practice_id = ?
        `;
        
        const [submissions] = await pool.query(submissionQuery, [practiceId]);
        
        // 格式化响应数据
        const formattedSubmissions = await Promise.all(submissions.map(async submission => {
            // 解密学生姓名和学号
            const decryptedName = submission.name ? 
                decryptForUser(submission.user_id, submission.name) : '未知学生';
                
            const decryptedStudentId = submission.student_id ? 
                decryptForUser(submission.user_id, submission.student_id) : '';

            // 获取练习总分
            const totalScoreQuery = `
                SELECT 
                    (
                        (SELECT IFNULL(SUM(score), 0) FROM choice_questions WHERE practice_id = ?) +
                        (SELECT IFNULL(SUM(blank1_score), 0) FROM fill_questions WHERE practice_id = ?) +
                        (SELECT IFNULL(SUM(blank2_score), 0) FROM fill_questions WHERE practice_id = ?) +
                        (SELECT IFNULL(SUM(blank3_score), 0) FROM fill_questions WHERE practice_id = ?) +
                        (SELECT IFNULL(SUM(score), 0) FROM essay_questions WHERE practice_id = ?)
                    ) AS total_score
            `;
            
            const [[{ total_score }]] = await pool.query(totalScoreQuery, [practiceId, practiceId, practiceId,practiceId,practiceId]);
            
            // 查询已提交且已评阅的解答题数量
            const [[{ judgedEssays }]] = await pool.query(
                'SELECT COUNT(*) as judgedEssays FROM commited_essay_question WHERE practice_id = ? AND commit_user_id = ? AND score IS NOT NULL',
                [practiceId, submission.commit_user_id]
            );
            
            // 查询已提交的解答题数量
            const [[{ submittedEssays }]] = await pool.query(
                'SELECT COUNT(*) as submittedEssays FROM commited_essay_question WHERE practice_id = ? AND commit_user_id = ?',
                [practiceId, submission.commit_user_id]
            );
            
            // 评阅状态判断：
            // 1. 如果学生没有提交任何解答题，默认已评阅
            // 2. 如果学生提交了解答题，检查是否所有提交的题目都已评阅
            const isJudged = submittedEssays === 0 || (submittedEssays > 0 && judgedEssays === submittedEssays);
            
            // 获取学生得分
            const studentScoreQuery = `
                SELECT 
                    (
                        (SELECT IFNULL(SUM(score), 0) FROM commited_choice_question WHERE practice_id = ? AND commit_user_id = ?) +
                        (SELECT IFNULL(SUM(score), 0) FROM commited_fill_question WHERE practice_id = ? AND commit_user_id = ?) +
                        (SELECT IFNULL(SUM(score), 0) FROM commited_essay_question WHERE practice_id = ? AND commit_user_id = ?)
                    ) AS obtained_score
            `;
            
            const [[{ obtained_score }]] = await pool.query(studentScoreQuery, [
                practiceId, submission.commit_user_id,
                practiceId, submission.commit_user_id,
                practiceId, submission.commit_user_id
            ]);
            
            return {
                user_id: submission.commit_user_id,
                name: decryptedName,  // 使用解密后的姓名
                student_id: decryptedStudentId,  // 使用解密后的学号
                submit_time : submission.submit_time,
                practice_id: submission.practice_id,
                practice_name: submission.practice_name,
                course_name: submission.course_name,
                deadline: submission.deadline,
                total_score,
                obtained_score,
                is_judged: isJudged
            };
        }));

        res.status(200).json(formattedSubmissions);
    } catch (error) {
        console.error('获取提交记录错误:', error);
        res.status(500).json({ error: '服务器内部错误' });
    }
});


// 最近提交时间
router.get('/practice/:practiceId/user/:userId/last-submit-time', async (req, res) => {
    const practiceId = req.params.practiceId;
    const userId = req.params.userId;
    //console.log(`收到请求: practiceId=${practiceId}, userId=${userId}`);
    try {
        // 执行SQL查询获取最近提交时间
        const [rows] = await pool.execute(
            'SELECT submit_time FROM commited_practice WHERE practice_id = ? AND commit_user_id = ? ORDER BY submit_time DESC LIMIT 1',
            [practiceId, userId]
        );
        
        if (rows.length === 0) {
            return res.status(200).json({ 
                success: false, 
                message: '未找到该练习的提交记录',
                data: null
            });
        }
        
        // 格式化时间为ISO字符串
        const lastSubmitTime = rows[0].submit_time;
        const formattedTime = new Date(lastSubmitTime).toISOString();
        
        res.json({ 
            success: true, 
            message: '获取最近提交时间成功',
            data: formattedTime
        });
        
    } catch (error) {
        console.error('查询最近提交时间失败:', error);
        res.status(500).json({ 
            success: false, 
            message: '服务器错误',
            error: error.message
        });
    }
});

//练习存在性检查（用于生成唯一练习id）
router.get('/practice/exists/:practiceId', async (req, res) => {
    const practiceId = req.params.practiceId;
    
    try {
        // 查询练习是否存在
        const [rows] = await pool.execute(
            'SELECT practice_id FROM practice WHERE practice_id = ?',
            [practiceId]
        );
        
        const exists = rows.length > 0;
        
        res.json({ 
            success: true,
            exists: exists,
            practiceId: practiceId
        });
        
    } catch (error) {
        console.error('查询练习是否存在失败:', error);
        res.status(500).json({ 
            success: false,
            message: '服务器错误',
            error: error.message
        });
    }
});
module.exports = router;
